#!/usr/bin/env python
# -*- coding: UTF-8 -*-

import MySQLdb
import sys
import re

## Categories

# 1: Hommes_Jeunes
# 2: Hommes_Adultes
# 3: Femmes_Jeunes
# 4: Femmes_Adultes
# 5: X-Files
# 6: Supprimees
# 7: Listes_Free

## Parametrage des listes (id_liste, id_categorie)

listes = [ 
         (5, 7),
         (12, 7),
         (14, 1),
         (30, 2),
         (31, 1),
         (32, 4),
       	 (33, 3),
       	 (34, 1),
       	 (35, 2),
       	 (36, 4),
       	 (37, 4),
       	 (38, 2),
       	 (39, 2),
       	 (42, 7),
       	 (43, 2),
       	 (45, 1),
       	 (50, 1),
       	 (51, 5),
       	 (54, 3),
       	 (55, 4),
       	 (56, 2),
       	 (57, 4),
         (999, 6),
]

## Connection aux bases de donnees

db_source = MySQLdb.connect(db = 'upsud', user='root', passwd = 'OhGheiw8EichahGh')
db_cible = MySQLdb.connect(db = 'megabase', user = 'root', passwd = 'OhGheiw8EichahGh')


## Parcours des listes

for liste in listes:
    
    cur = db_source.cursor()
    print('\n---------Extraction de la liste ' + str(liste[0]) + ' --------')
    if liste[0] == 999:
        cur.execute("SELECT `emailaddress` FROM `em_banned_emails`")
    else:
        cur.execute("SELECT `emailaddress` FROM `em_list_subscribers` WHERE `listid`=" + str(liste[0]) + " AND `bounced`=0")

    ## Lecture des resultats

    test = 0

    round = cur.fetchone()
    print('\nLecture et traitement des resultats')
    while (round != None):

        ## Lecture de l'adresse

        if re.search("@", round[0], re.I):
            current_email = round[0]
            current_prefix = round[0].split('@')[0]
            current_suffix = round[0].split('@')[1]

            ## Test d'existence de l'adresse

            abort = False
            cur2 = db_cible.cursor()
            cur2.execute("SELECT `adresse` FROM `adresses` WHERE `adresse`='" + current_email + "'")
            if cur2.fetchone() != None:
                abort = True
                if liste[0] == 999:
                    cur999 = db_cible.cursor()
                    cur999.execute("UPDATE `adresses` SET `categorie`=6 WHERE `adresse`='" + current_email + "'")
            cur2.close()

            ## Test du domaine (dedoublonnage)

            if (current_suffix == 'orange.fr'):
                cur3 = db_cible.cursor()
                cur3.execute("SELECT `adresse` FROM `adresses` WHERE `adresse`='" + current_prefix + "@" + "wanadoo.fr'")
                if cur3.fetchone() != None:
                    abort = True
                    cur4 = db_cible.cursor()
                    cur4.execute("UPDATE `adresses` SET `adresse`='" + current_email + "' WHERE `adresse`='" + current_prefix + "@" + "wanadoo.fr'")
                    cur4.close()
                cur3.close()

            if (current_suffix == 'wanadoo.fr'):
                cur3 = db_cible.cursor()
                cur3.execute("SELECT `adresse` FROM `adresses` WHERE `adresse`='" + current_prefix + "@" + "orange.fr'")
                if cur3.fetchone() != None:
                    abort = True
                cur3.close()

            ## Injection de l'adresse

            if abort == False:
                cur5 = db_cible.cursor()
                cur5.execute("INSERT INTO `adresses` (adresse, categorie) VALUES ('" + current_email + "', '" + str(liste[1]) + "')")
                cur5.close()

        ## Passage a l'iteration suivante

        round = cur.fetchone()

    ## Application des modifications

#    print('\n-------------------Commit--------------------\n')
#    db_cible.commit()

## Application des modifications

db_cible.commit()

## Fermeture du curseur principal

cur.close()

## Fin du script

print('\n---------------------------------------------\n')
print('\nFini !!\n\n')

sys.exit(0)
